if exists (select 1 from sysobjects where name = 'get_personferienrow' and type = 'P')
begin
   drop procedure get_personferienrow
   print 'Procedure: get_personferienrow deleted ...'
end
go
create procedure get_personferienrow(
  @prsid      int = 1,
  @jahr       int = 2008
)
as
begin
  set nocount on

  select ps.AbteilungID, 
         a.Name, 
         ap.PlanID, 
         ap.Name 
    from PlanSchema ps
    left outer join ArbeitsPlan ap
      on ap.SchemaID = ps.SchemaID
    left outer join PlanungsJahr pj
      on pj.JahrID = ap.JahrID
    left outer join Abteilung a
      on a.AbteilungID = ps.AbteilungID
   where ps.PrsID = @prsid
     and ap.PlanID is not null
     and pj.Jahr = @jahr
   order by ps.AbteilungID, ap.PlanID

end
go
print 'Procedure: get_personferienrow done ...'
go

grant exec on get_personferienrow to prsadmins with grant option
go
grant exec on get_personferienrow to prsusers
go

